package com.xmh.code.generation.util;

import com.alibaba.fastjson.JSONArray;
import com.xmh.code.generation.GenerationApplication;
import com.xmh.code.generation.inject.Inject;
import com.xmh.code.generation.vo.ColumnVo;
import com.xmh.code.generation.vo.TableVo;
import org.apache.commons.lang3.StringUtils;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.stereotype.Component;
import org.springframework.test.context.junit4.SpringRunner;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RunWith(SpringRunner.class)
@Component
@SpringBootTest(classes = GenerationApplication.class)
public class DataUtils {

    /**
     * 跟进表名字数组获取到对应表的具体数据，数组为空则获取整个数据库
     *
     * @param table_Name_List 表名字数组
     * @return 请看debug
     * @throws SQLException sql异常
     */
    public static List<TableVo> getTableList(String[] table_Name_List) throws SQLException {
        // 获得元数据
        Connection conn = Inject.getDruidDataSource().getConnection();
//        DruidPooledConnection conn = connection;

        DatabaseMetaData metaData = conn.getMetaData();
        // 先判断有没有指定获取哪个表格，没有的话就获取全部
        List<TableVo> tableVos = getTablesMap(table_Name_List, metaData);

        List<TableVo> result = new ArrayList<>();

        //查询每个表的字段
        for (TableVo tableVo : tableVos) {
            // 先用SQL查询出所有字段，防止getColumns方法重复
            String tableName = tableVo.getName();

            Map<String, Integer> column = getColumn(conn, tableName);

            List<ColumnVo> list = new ArrayList<>();

            ResultSet columns = metaData.getColumns(null, null, tableName, "%");

            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");

                Integer value = column.get(columnName);
                if (value == null || value == 2) {
                    continue;
                } else {
                    column.put(columnName, 2);
                }
                list.add(new ColumnVo(columnName, columns.getString("TYPE_NAME"), columns.getString("REMARKS")));
            }
            tableVo.setColumnList(list);
            result.add(tableVo);
        }
        return result;
    }


    private static List<TableVo> getTablesMap(String[] table_Name_List, DatabaseMetaData metaData) throws SQLException {
        if (table_Name_List.length == 0) {
            // 从元数据中获取到所有的表名极其表格注释
            return getTableNameRemarks(metaData);
        } else {
            List<TableVo> list = new ArrayList<>();
            for (String tableName : table_Name_List) {
                TableVo tableVo = new TableVo();
                tableVo.setName(tableName);
                list.add(tableVo);
            }
            return list;
        }
    }

    /**
     * 从元数据中获取到所有的表名极其表格注释
     *
     * @param metaData 元数据
     * @return 结果map
     * @throws SQLException sql 异常
     */
    private static List<TableVo> getTableNameRemarks(DatabaseMetaData metaData) throws SQLException {
        //从元数据中获取到所有的表名
        ResultSet rs = metaData.getTables(null, null, null, new String[]{"TABLE"});

        String dataBaseName = StringUtils.substringBetween(metaData.getURL(), ":3306/", "?");

        List<TableVo> list = new ArrayList<>();

        while (rs.next()) {
            String table_cat = rs.getString("TABLE_CAT");
            if (dataBaseName.equals(table_cat)) {
                TableVo tableVo = new TableVo();
                tableVo.setName(rs.getString("TABLE_NAME"));
                tableVo.setType(rs.getString("TABLE_TYPE"));
                tableVo.setCat(table_cat);
                tableVo.setSchem(rs.getString("TABLE_SCHEM"));
                tableVo.setRemarks(rs.getString("REMARKS"));
                list.add(tableVo);
            }
        }
        return list;
    }

    private static Map<String, Integer> getColumn(Connection conn, String tableName) throws SQLException {
        String sql = "select * from " + tableName;

        ResultSetMetaData meta = conn.prepareStatement(sql).executeQuery().getMetaData();

        Map<String, Integer> map = new HashMap<>();
        for (int i = 1; i <= meta.getColumnCount(); i++) {
            map.put(meta.getColumnName(i), 1);
//            map.put(meta.getColumnName(i), meta.getColumnTypeName(i));
        }
        return map;
    }

    private List<TableVo> getTableList() {
        String tableJson = "[{\"columnList\":[{\"name\":\"id\",\"remarks\":\"\",\"type\":\"INT\"},{\"name\":\"real_name\",\"remarks\":\"\",\"type\":\"VARCHAR\"},{\"name\":\"nick_name\",\"remarks\":\"\",\"type\":\"VARCHAR\"}],\"name\":\"t_author\",\"remarks\":\"\"},{\"columnList\":[{\"name\":\"id\",\"remarks\":\"\",\"type\":\"BIGINT\"},{\"name\":\"password\",\"remarks\":\"密码\",\"type\":\"VARCHAR\"},{\"name\":\"username\",\"remarks\":\"\",\"type\":\"VARCHAR\"},{\"name\":\"nickname\",\"remarks\":\"\",\"type\":\"VARCHAR\"},{\"name\":\"user_enabled\",\"remarks\":\"\",\"type\":\"BIT\"},{\"name\":\"create_date\",\"remarks\":\"\",\"type\":\"DATETIME\"},{\"name\":\"update_date\",\"remarks\":\"\",\"type\":\"DATETIME\"}],\"name\":\"t_user\",\"remarks\":\"\"},{\"columnList\":[{\"name\":\"id\",\"remarks\":\"\",\"type\":\"INT\"},{\"name\":\"userid\",\"remarks\":\"\",\"type\":\"INT\"},{\"name\":\"name\",\"remarks\":\"\",\"type\":\"VARCHAR\"},{\"name\":\"address\",\"remarks\":\"\",\"type\":\"VARCHAR\"},{\"name\":\"img1\",\"remarks\":\"\",\"type\":\"VARCHAR\"},{\"name\":\"img2\",\"remarks\":\"\",\"type\":\"VARCHAR\"},{\"name\":\"img3\",\"remarks\":\"\",\"type\":\"VARCHAR\"},{\"name\":\"img_all\",\"remarks\":\"\",\"type\":\"VARCHAR\"},{\"name\":\"qq\",\"remarks\":\"\",\"type\":\"VARCHAR\"},{\"name\":\"wechat\",\"remarks\":\"\",\"type\":\"VARCHAR\"},{\"name\":\"phone\",\"remarks\":\"\",\"type\":\"VARCHAR\"},{\"name\":\"update_date\",\"remarks\":\"\",\"type\":\"DATETIME\"},{\"name\":\"create_date\",\"remarks\":\"\",\"type\":\"DATETIME\"}],\"name\":\"t_user_qianmei\",\"remarks\":\"\"},{\"columnList\":[{\"name\":\"id\",\"remarks\":\"\",\"type\":\"INT\"},{\"name\":\"cost_date\",\"remarks\":\"\",\"type\":\"DATETIME\"},{\"name\":\"amount\",\"remarks\":\"\",\"type\":\"DECIMAL\"},{\"name\":\"type_id\",\"remarks\":\"\",\"type\":\"INT\"},{\"name\":\"create_date\",\"remarks\":\"\",\"type\":\"DATETIME\"},{\"name\":\"update_date\",\"remarks\":\"\",\"type\":\"DATETIME\"}],\"name\":\"t_spending_plan\",\"remarks\":\"\"},{\"columnList\":[{\"name\":\"id\",\"remarks\":\"\",\"type\":\"INT\"},{\"name\":\"name\",\"remarks\":\"\",\"type\":\"VARCHAR\"},{\"name\":\"bucket_name\",\"remarks\":\"\",\"type\":\"VARCHAR\"},{\"name\":\"price\",\"remarks\":\"\",\"type\":\"DECIMAL\"},{\"name\":\"object_key\",\"remarks\":\"\",\"type\":\"VARCHAR\"},{\"name\":\"size\",\"remarks\":\"\",\"type\":\"BIGINT\"},{\"name\":\"update_date\",\"remarks\":\"\",\"type\":\"DATETIME\"},{\"name\":\"create_date\",\"remarks\":\"\",\"type\":\"DATETIME\"}],\"name\":\"t_video\",\"remarks\":\"\"}]";

        JSONArray objects = JSONArray.parseArray(tableJson);

        List<TableVo> tableVoList = objects.toJavaList(TableVo.class);

        for (TableVo tableVo : tableVoList) {
            JSONArray ColumnJSONArray = new JSONArray((List) tableVo.getColumnList());

            tableVo.setColumnList(ColumnJSONArray.toJavaList(ColumnVo.class));
        }

        return tableVoList;

//        List<JSONObject> tableList = JSONObject.parseObject(tableJson, List.class);
//
//        List<TableVo> tableLists = new ArrayList<>();
//
//        for (JSONObject jsonObject : tableList) {
//            TableVo tableVo = JSON.toJavaObject(jsonObject, TableVo.class);
//
//            List<ColumnVo> columnVoList = new ArrayList<>();
//
//            for (Object obj : tableVo.getColumnList()) {
//                JSONObject jsonObject1 = (JSONObject) obj;
//
//                columnVoList.add(JSON.toJavaObject(jsonObject1, ColumnVo.class));
//            }
//
//            tableVo.setColumnList(columnVoList);
//
//            tableLists.add(tableVo);
//        }
//        return tableLists;
    }

}
